IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spArchives_RetrieveAll')
	BEGIN
		PRINT 'Dropping Procedure spArchives_RetrieveAll'
		DROP  Procedure  spArchives_RetrieveAll
	END

GO

PRINT 'Creating Procedure spArchives_RetrieveAll'
GO

CREATE Procedure spArchives_RetrieveAll
	(
		@Archive_ID int = null, 
		@CreatedByUser_ID numeric(9,0) = null, 
		@ArchiveDate DateTime = null, 
		@ArchiveStatus varchar(50) = null, 
		@DivisionName varchar(50) = null, 
		@DepartmentName varchar(50) = null, 
		@AttachedFile image = null, 
		@Comments varchar(250) = null, 
		@DestructionDate DateTime = null, 
		@Shred numeric = null, 
		@NoShredReason varchar(100) = null, 
		@CurrentLocation varchar(100) = null, 
		@DeliveryCollection varchar(100) = null, 
		@DueDate DateTime = null, 
		@ApprovedUser_ID numeric(9,0) = null, 
		@ApprovedDate DateTime = null, 
		@ReceivedbyUser_ID numeric(9,0) = null, 
		@ReceivedDate DateTime = null, 
		@ClosedByUser_ID numeric(9,0) = null, 
		@ClosedDate DateTime = null
	)
AS
    /* 
    ------------------------------------------------------------------------------
    - Change History
    - --------------
    - Date          Description
    ------------------------------------------------------------------------------
    - 2007/11/02	Initial version created
    ------------------------------------------------------------------------------
    */
	
	SET NOCOUNT ON
	
	-- Retrieve the relevant records
	SELECT  *
	FROM	Archives
	
	WHERE	(@Archive_ID IS NULL OR Archives.Archive_ID = @Archive_ID)
	  AND	(@CreatedByUser_ID IS NULL OR Archives.CreatedByUser_ID = @CreatedByUser_ID)
	  AND	(@ArchiveDate IS NULL OR Archives.ArchiveDate = @ArchiveDate)
	  AND	(@ArchiveStatus IS NULL OR Archives.ArchiveStatus LIKE @ArchiveStatus + '%')
	  AND	(@DivisionName IS NULL OR Archives.DivisionName LIKE @DivisionName + '%')
	  AND	(@DepartmentName IS NULL OR Archives.DepartmentName LIKE @DepartmentName + '%')
	  AND	(@AttachedFile IS NULL OR Archives.AttachedFile = @AttachedFile)
	  AND	(@Comments IS NULL OR Archives.Comments LIKE @Comments + '%')
	  AND	(@DestructionDate IS NULL OR Archives.DestructionDate = @DestructionDate)
	  AND	(@Shred IS NULL OR Archives.Shred = @Shred)
	  AND	(@NoShredReason IS NULL OR Archives.NoShredReason LIKE @NoShredReason + '%')
	  AND	(@CurrentLocation IS NULL OR Archives.CurrentLocation LIKE @CurrentLocation + '%')
	  AND	(@DeliveryCollection IS NULL OR Archives.DeliveryCollection LIKE @DeliveryCollection + '%')
	  AND	(@DueDate IS NULL OR Archives.DueDate = @DueDate)
	  AND	(@ApprovedUser_ID IS NULL OR Archives.ApprovedUser_ID = @ApprovedUser_ID)
	  AND	(@ApprovedDate IS NULL OR Archives.ApprovedDate = @ApprovedDate)
	  AND	(@ReceivedbyUser_ID IS NULL OR Archives.ReceivedbyUser_ID = @ReceivedbyUser_ID)
	  AND	(@ReceivedDate IS NULL OR Archives.ReceivedDate = @ReceivedDate)
	  AND	(@ClosedByUser_ID IS NULL OR Archives.ClosedByUser_ID = @ClosedByUser_ID)
	  AND	(@ClosedDate IS NULL OR Archives.ClosedDate = @ClosedDate)

	-- Return to calling method
	RETURN
GO
